<?php session_start();
include "constants.php";
$dates=date_create(CUTOFFS);
$cutoffdates=date_format($dates,"m/d/Y");
$INITIALS=SPOINT;

$newip = $_SERVER['REMOTE_ADDR'];
$Employee=$_SESSION['Username'];



if(!isset($Employee)||empty($Employee)||$newip!= $_SESSION['IP'])
{echo "If you are payday loan applicant, please start your ". "<a href='apply.php'>application here</a> now, thank you for your cooperation!";
exit();
}


if(!isset($Employee)||empty($Employee)||$newip!= $_SESSION['IP'])
{echo "If you are payday loan applicant, please start your ". "<a href='../apply.php'>application here</a> now, thank you for your cooperation!";
exit();
}

$link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD); 

if (!$link) { 
    die('Could not connect: ' . mysql_error()); 
} 
mysql_select_db(DB_NAME);


$mstatus = $_GET['mstatus'];
$startmonth = $_GET['startmonth'];
$endmonth = $_GET['endmonth'];
	// Escape User Input to help prevent SQL Injection
$status = mysql_real_escape_string($mstatus);
$startmonth = mysql_real_escape_string($startmonth);
$endmonth = mysql_real_escape_string($endmonth);

if($startmonth!=$endmonth) {$Results=" between ".$startmonth." and ".$endmonth;}
else {$Results=" on ".$startmonth;}


if($mstatus=="1")
{
$reports="Monthly Report";
	//build query
$query = "
SELECT Date_Format(ApprovalDate ,'%M%Y' ) AS DATES,COUNT(*) as TotalAPPS, 
        SUM(CASE NewApp WHEN '5' THEN 0 WHEN '1' THEN 0 ELSE 1 END) AS APPROVED_APPS, 
	SUM(CASE NewApp WHEN '5' THEN 1 ELSE 0 END) AS DECLINED_APPS, 
        SUM(CASE NewApp WHEN '5' THEN 0 WHEN '1' THEN 0 ELSE ApprovalAmount END) AS APPROVED_LOANS, 
        SUM(CASE NewApp WHEN '5' THEN 0 WHEN '1' THEN 0 ELSE ApprovalAmount*COST_100/100 END) AS APPROVED_CHARGES, 
        SUM(CASE NewApp WHEN '2' THEN PaidAmount ELSE 0 END) AS TotalPAYMENT,
	SUM(CASE NewApp WHEN '5' THEN 0 WHEN '1' THEN 0 WHEN '2' THEN PaidAmount-ApprovalAmount ELSE -ApprovalAmount END) AS REVENUES
FROM Applications
WHERE Datediff(ApprovalDate  , STR_TO_DATE('$startmonth','%m/%d/%Y' ))>=0 AND
			Datediff(ApprovalDate  , STR_TO_DATE('$endmonth','%m/%d/%Y' ))<=0 AND Datediff(SignDate , STR_TO_DATE('$cutoffdates' , '%m/%d/%Y' ))>0
GROUP BY Date_Format(ApprovalDate , '%M%Y' )
ORDER BY Date_Format(ApprovalDate , '%Y' ) DESC , Date_Format(ApprovalDate ,'%m' ) DESC";

}
else if($mstatus=="2")
{
$reports="Yearly Report";
	//build query
$query = "
SELECT Date_Format(ApprovalDate ,'%Y' ) AS DATES,COUNT(*) as TotalAPPS, 
        SUM(CASE NewApp WHEN '5' THEN 0 WHEN '1' THEN 0 ELSE 1 END) AS APPROVED_APPS, 
	SUM(CASE NewApp WHEN '5' THEN 1 ELSE 0 END) AS DECLINED_APPS, 
        SUM(CASE NewApp WHEN '5' THEN 0 WHEN '1' THEN 0 ELSE ApprovalAmount END) AS APPROVED_LOANS, 
        SUM(CASE NewApp WHEN '5' THEN 0 WHEN '1' THEN 0 ELSE ApprovalAmount*COST_100/100 END) AS APPROVED_CHARGES, 
        SUM(CASE NewApp WHEN '2' THEN PaidAmount ELSE 0 END) AS TotalPAYMENT,
	SUM(CASE NewApp WHEN '5' THEN 0 WHEN '1' THEN 0 WHEN '2' THEN PaidAmount-ApprovalAmount ELSE -ApprovalAmount END) AS REVENUES
FROM Applications
WHERE Datediff(ApprovalDate , STR_TO_DATE('$startmonth','%m/%d/%Y' ))>=0 AND
			Datediff(SignDate, STR_TO_DATE('$endmonth','%m/%d/%Y' ))<=0 AND Datediff(SignDate , STR_TO_DATE('$cutoffdates' , '%m/%d/%Y' ))>0
GROUP BY Date_Format(ApprovalDate , '%Y' )
ORDER BY Date_Format(ApprovalDate , '%Y' ) DESC";

} else if($mstatus=="4")
{
$reports="Approved Applications";
	//build query
$query = "
SELECT Date(ApprovalDate) AS DATES,AccountID,BankName,ApprovalAmount,TotalCost,CollectDate,
        CASE NewApp WHEN '2' THEN 'PAID' WHEN '3' THEN 'PAD' 
		    WHEN '4' THEN 'NSF' WHEN '9' THEN 'Agent' WHEN '8' THEN 'Default'
		    WHEN '7' THEN 'NO PAD yet' END AS Status, PaidAmount,Employee
FROM Applications
WHERE Datediff(ApprovalDate , STR_TO_DATE('$startmonth','%m/%d/%Y' ))>=0 AND
			Datediff(SignDate, STR_TO_DATE('$endmonth','%m/%d/%Y' ))<=0 AND Datediff(SignDate , STR_TO_DATE('$cutoffdates' , '%m/%d/%Y' ))>0
	AND (NewApp<>1 AND NewApp <>5)";

}

if($mstatus=="1" || $mstatus=="2") {

$qry_result = mysql_query($query) or die(mysql_error());

	//Build Result String
$display_string = "<table><table cellspacing='4' cellpadding='1' border='1' style='margin: 0 0 0 0;'>";
$display_string .= "<tr><th width='640' align='center' colspan='8'>".$reports."</th></tr>";
$display_string .= "<tr>";
$display_string .= "<th width='80' align='center'>Date</th>";
$display_string .= "<th width='80' align='center'>#of Applications</th>";
$display_string .= "<th width='80' align='center'>#of Approved</th>";
$display_string .= "<th width='80' align='center'>#of Declined</th>";
$display_string .= "<th width='80' align='center'>Approved Loans</th>";
$display_string .= "<th width='80' align='center'>Loan Charges</th>";
$display_string .= "<th width='80' align='center'>Total Payment</th>";
$display_string .= "<th width='80' align='center'>Revenues</th>";
$display_string .= "</tr>";

// Insert a new row in the table for each person returned
while($row = mysql_fetch_array($qry_result)){
	$display_string .= "<tr>";
	$display_string .= "<td  width='80' align='center'>$row[DATES]</td>";
	$display_string .= "<td  width='80' align='center'>$row[TotalAPPS]</td>";
	$display_string .= "<td  width='80' align='center'>$row[APPROVED_APPS]</td>";
	$display_string .= "<td  width='80' align='center'>$row[DECLINED_APPS]</td>";
	$display_string .= "<td  width='80' align='center'>$row[APPROVED_LOANS]</td>";
	$display_string .= "<td  width='80' align='center'>$row[APPROVED_CHARGES]</td>";
	$display_string .= "<td  width='80' align='center'>$row[TotalPAYMENT]</td>";
	$display_string .= "<td  width='80' align='center'>$row[REVENUES]</td>";
	$display_string .= "</tr>";
	
}
//echo "Query: " . $query . "<br />";
$display_string .= "</table>";
echo $display_string;}

else if($mstatus=="4") {
$orders=1;
$qry_result = mysql_query($query) or die(mysql_error());

	//Build Result String
$display_string = "<table><table cellspacing='4' cellpadding='1' border='1' style='margin: 0 0 0 0;'>";
$display_string .= "<tr><th width='720' align='center' colspan='9'>".$reports.$Results."</th></tr>";
$display_string .= "<tr>";
$display_string .= "<th width='80' align='center'>Series</th>";
$display_string .= "<th width='80' align='center'>AccountID</th>";
$display_string .= "<th width='80' align='center'>Bank</th>";
$display_string .= "<th width='80' align='center'>Loan</th>";
$display_string .= "<th width='80' align='center'>Charges</th>";
$display_string .= "<th width='80' align='center'>Due Day</th>";
$display_string .= "<th width='80' align='center'>Status</th>";
$display_string .= "<th width='80' align='center'>Paid</th>";
$display_string .= "<th width='80' align='center'>Employee</th>";
$display_string .= "</tr>";

// Insert a new row in the table for each person returned
while($row = mysql_fetch_array($qry_result)){
	$aid=$row[AccountID]-$INITIALS;
	$display_string .= "<tr>";
	$display_string .= "<td  width='80' align='center'>$orders</td>";
	$display_string .= "<td  width='80' align='center'>$aid</td>";
	$display_string .= "<td  width='80' align='center'>$row[BankName]</td>";
	$display_string .= "<td  width='80' align='center'>$row[ApprovalAmount]</td>";
	$display_string .= "<td  width='80' align='center'>$row[TotalCost]</td>";
	$display_string .= "<td  width='80' align='center'>$row[CollectDate]</td>";
	$display_string .= "<td  width='80' align='center'>$row[Status]</td>";
	$display_string .= "<td  width='80' align='center'>$row[PaidAmount]</td>";
	$display_string .= "<td  width='80' align='center'>$row[Employee]</td>";
	$display_string .= "</tr>";
$orders=$orders+1;
}
//echo "Query: " . $query . "<br />";
$display_string .= "</table>";
echo $display_string;}


?>